package org.springblade.modules.notice.constant;

/**
 * @Author ysq
 * @Date 2024/11/27
 * @Description 维修变量常量
 */
public interface RepairVariableConstant {

	/**
	 * 维修工单基础信息
	 */
	String WORKER_INFO = "SELECT\n" +
		"\tt1.id AS planId,\n" +
		"\tt1.elevator_id AS elevatorId,\n" +
		"\t\tDATE_FORMAT( t1.plan_date, '%Y年%m月%d日 ' ) AS planDate,\n" +
		"\tCONCAT(\n" +
		"\t\tDATE_FORMAT( t1.finish_date, '%Y年%m月%d日 ' ),\n" +
		"\tCASE\n" +
		"\t\t\tDAYOFWEEK( t1.finish_date ) \n" +
		"\t\t\tWHEN 1 THEN\n" +
		"\t\t\t'周日' \n" +
		"\t\t\tWHEN 2 THEN\n" +
		"\t\t\t'周一' \n" +
		"\t\t\tWHEN 3 THEN\n" +
		"\t\t\t'周二' \n" +
		"\t\t\tWHEN 4 THEN\n" +
		"\t\t\t'周三' \n" +
		"\t\t\tWHEN 5 THEN\n" +
		"\t\t\t'周四' \n" +
		"\t\t\tWHEN 6 THEN\n" +
		"\t\t\t'周五' \n" +
		"\t\t\tWHEN 7 THEN\n" +
		"\t\t\t'周六' \n" +
		"\t\tEND,\n" +
		"\t\t' ',\n" +
		"\t\tDATE_FORMAT( t1.finish_date, '%H:%i' ) \n" +
		"\t) AS estimatedEndTime,\n" +
		"\tDATE_FORMAT( t1.create_time, '%Y-%m-%d %H:%i' ) AS createTime,\n" +
		"\tt3.elevator_address AS elevatorAddress,\n" +
		"\tt4.NAME AS projectName \n" +
		"FROM\n" +
		"\tblade_maintain_plan_content t1\n" +
		"\tLEFT JOIN blade_elevator t2 ON t1.elevator_id = t2.id\n" +
		"\tLEFT JOIN blade_elevator_detail t3 ON t2.id = t3.elevator_id\n" +
		"\tLEFT JOIN blade_building t4 ON t2.building_id = t4.id \n" +
		"WHERE\n" +
		"\tt1.id = ?";

	/**
	 * 维修工单扩展信息
	 */
	String WORKER_EXT_INFO = "SELECT\n" +
		"\tt1.allocate_time AS userAssignTime,\n" +
		"\tt1.arrive_time AS firstSignInTime,\n" +
		"\tt1.plan_end_time AS planEndTime,\n" +
		"\tt3.mf_desc AS repairDesc,\n" +
		"\tt3.name AS reporter,\n" +
		"\tt3.phone AS malPhone\n" +
		"FROM\n" +
		"\tblade_maintain_plan_content_ext t1\n" +
		"\tLEFT JOIN ( SELECT plan_id, min( id ) id FROM blade_maintain_malfunction_work WHERE is_deleted = 0 GROUP BY plan_id ) t2 ON t1.plan_id = t2.plan_id\n" +
		"\tLEFT JOIN blade_maintain_malfunction_work t3 ON t2.id = t3.id \n" +
		"WHERE\n" +
		"\tt1.is_deleted = 0 \n" +
		"\tAND t1.plan_id = ?";

	/**
	 * 维修工单审批信息
	 */
	String WORKER_APPROVE = "SELECT\n" +
		"\tt1.id AS approveId,\n" +
		"\tt1.remark AS approvalFeedback \n" +
		"FROM\n" +
		"\tblade_business_approve t1 \n" +
		"WHERE\n" +
		"\tt1.type = 6 \n" +
		"\tAND t1.is_deleted = 0 \n" +
		"\tAND t1.work_order_id = ?";

	/**
	 * 执行人
	 */
	String EXECUTOR = "SELECT\n" +
		"\tGROUP_CONCAT( t2.real_name separator '/') AS executor \n" +
		"FROM\n" +
		"\tblade_maintain_plan_content_user t1\n" +
		"\tLEFT JOIN blade_user t2 ON t1.user_id = t2.id \n" +
		"WHERE\n" +
		"\tt1.is_deleted = 0 \n" +
		"\tAND t1.plan_id = ?\n" +
		"\tGROUP BY t1.plan_id";

	/**
	 * 首次签到人
	 */
	String FIRST_SIGN_INFO = "SELECT\n" +
		"\tt2.real_name AS firstSignInUser \n" +
		"FROM\n" +
		"\tblade_maintain_sign t1\n" +
		"\tLEFT JOIN blade_user t2 ON t1.user_id = t2.id \n" +
		"WHERE\n" +
		"\tt1.is_deleted = 0 \n" +
		"\tAND t1.plan_id = ? \n" +
		"ORDER BY\n" +
		"\tt1.id \n" +
		"\tLIMIT 1";

	/**
	 * 未签退人员名称
	 */
	String NOT_SIGN_OUT_USER_NAMES = "SELECT\n" +
		"\tGROUP_CONCAT( t2.real_name ) notSignOutUserNames \n" +
		"FROM\n" +
		"\tblade_maintain_sign t1\n" +
		"\tLEFT JOIN blade_user t2 ON t2.id = t1.user_id \n" +
		"WHERE\n" +
		"\tt1.plan_id = ?\n" +
		"\tAND t1.sign_out_time IS NULL \n" +
		"GROUP BY\n" +
		"\tt1.plan_id";

	/**
	 * 首次签退信息
	 */
	String FIRST_SIGN_OUT_INFO = "SELECT\n" +
		"\tt1.sign_out_time AS fistSignOutTime,\n" +
		"\tt3.real_name AS firstSignOutUserName \n" +
		"FROM\n" +
		"\tblade_maintain_sign t1\n" +
		"\tINNER JOIN ( SELECT id FROM blade_maintain_sign WHERE is_deleted = 0 AND plan_id = ? ORDER BY sign_out_time LIMIT 1 ) t2 ON t1.id = t2.id\n" +
		"\tLEFT JOIN blade_user t3 ON t1.user_id = t3.id";

	/**
	 * 故障现象
	 */
	String FAULT_PROBLEM = "SELECT\n" +
		"\tGROUP_CONCAT( t2.dict_value SEPARATOR '、' ) AS faultProblem \n" +
		"FROM\n" +
		"\t(\n" +
		"\tSELECT\n" +
		"\t\tsubstring_index( substring_index( a.problem_item, ',', b.help_topic_id + 1 ), ',',- 1 ) AS problem \n" +
		"\tFROM\n" +
		"\t\tblade_maintain_malfunction_work a\n" +
		"\t\tJOIN mysql.help_topic b ON b.help_topic_id < ( length( a.problem_item ) - length( REPLACE ( a.problem_item, ',', '' ) ) + 1 ) \n" +
		"\tWHERE\n" +
		"\t\ta.plan_id = ? \n" +
		"\t) t1\n" +
		"\tLEFT JOIN blade_dict t2 ON t1.problem = t2.dict_key \n" +
		"\tAND t2.CODE = 'fault_problem' \n" +
		"GROUP BY\n" +
		"\tt2.CODE";
}
